from sqlalchemy import Column, Integer, String, Boolean, DateTime, Text, ForeignKey, Enum, JSON
from sqlalchemy.orm import relationship
from sqlalchemy.sql import func
from app.database import Base
import enum


class DataSourceType(str, enum.Enum):
    """数据源类型枚举"""
    MYSQL = "mysql"
    POSTGRESQL = "postgresql"
    ORACLE = "oracle"
    SQLSERVER = "sqlserver"
    DORIS = "doris"          # 添加 Doris 数据仓库类型
    MONGODB = "mongodb"
    REDIS = "redis"
    ELASTICSEARCH = "elasticsearch"
    KAFKA = "kafka"
    HDFS = "hdfs"
    HIVE = "hive"
    SPARK = "spark"
    FTP = "ftp"
    HTTP_API = "http_api"
    FILE = "file"


class ConnectionStatus(str, enum.Enum):
    """连接状态枚举"""
    ACTIVE = "active"
    INACTIVE = "inactive"
    ERROR = "error"
    TESTING = "testing"


class SyncType(str, enum.Enum):
    """同步类型枚举"""
    FULL = "full"          # 全量同步
    INCREMENTAL = "incremental"  # 增量同步
    REALTIME = "realtime"   # 实时同步


class TaskStatus(str, enum.Enum):
    """任务状态枚举"""
    PENDING = "pending"
    RUNNING = "running"
    SUCCESS = "success"
    FAILED = "failed"
    CANCELLED = "cancelled"


class DataSource(Base):
    """数据源模型"""
    __tablename__ = "datasources"

    id = Column(Integer, primary_key=True, index=True)
    name = Column(String(100), nullable=False, index=True)
    description = Column(Text, nullable=True)
    source_type = Column(Enum(DataSourceType), nullable=False)
    
    # 连接配置
    host = Column(String(255), nullable=True)
    port = Column(Integer, nullable=True)
    database = Column(String(100), nullable=True)
    username = Column(String(100), nullable=True)
    password = Column(String(255), nullable=True)  # 加密存储
    connection_params = Column(JSON, nullable=True)  # 额外连接参数
    
    # 状态信息
    status = Column(Enum(ConnectionStatus), default=ConnectionStatus.INACTIVE)
    is_active = Column(Boolean, default=True)
    last_test_time = Column(DateTime(timezone=True), nullable=True)
    last_test_result = Column(Text, nullable=True)
    
    # 创建信息
    creator_id = Column(Integer, ForeignKey("users.id"), nullable=False)
    created_at = Column(DateTime(timezone=True), server_default=func.now())
    updated_at = Column(DateTime(timezone=True), onupdate=func.now())
    
    # 关系
    creator = relationship("User")
    tables = relationship("DataTable", back_populates="datasource")
    sync_tasks = relationship("DataSyncTask", back_populates="source_datasource")
    target_sync_tasks = relationship("DataSyncTask", back_populates="target_datasource")


class DataTable(Base):
    """数据表模型"""
    __tablename__ = "data_tables"

    id = Column(Integer, primary_key=True, index=True)
    datasource_id = Column(Integer, ForeignKey("datasources.id"), nullable=False)
    table_name = Column(String(100), nullable=False)
    schema_name = Column(String(100), nullable=True)
    table_comment = Column(Text, nullable=True)
    
    # 表结构信息
    columns_info = Column(JSON, nullable=True)  # 列信息 JSON
    row_count = Column(Integer, nullable=True)
    data_size = Column(String(50), nullable=True)  # 数据大小
    
    # 更新信息
    last_updated = Column(DateTime(timezone=True), nullable=True)
    created_at = Column(DateTime(timezone=True), server_default=func.now())
    
    # 关系
    datasource = relationship("DataSource", back_populates="tables")
    columns = relationship("DataColumn", back_populates="table")


class DataColumn(Base):
    """数据列模型"""
    __tablename__ = "data_columns"

    id = Column(Integer, primary_key=True, index=True)
    table_id = Column(Integer, ForeignKey("data_tables.id"), nullable=False)
    column_name = Column(String(100), nullable=False)
    data_type = Column(String(50), nullable=False)
    is_nullable = Column(Boolean, default=True)
    is_primary_key = Column(Boolean, default=False)
    default_value = Column(String(255), nullable=True)
    column_comment = Column(Text, nullable=True)
    
    # 数据质量信息
    null_count = Column(Integer, nullable=True)
    unique_count = Column(Integer, nullable=True)
    min_value = Column(String(255), nullable=True)
    max_value = Column(String(255), nullable=True)
    
    created_at = Column(DateTime(timezone=True), server_default=func.now())
    
    # 关系
    table = relationship("DataTable", back_populates="columns")


class DataSyncTask(Base):
    """数据同步任务模型"""
    __tablename__ = "data_sync_tasks"

    id = Column(Integer, primary_key=True, index=True)
    name = Column(String(100), nullable=False, index=True)
    description = Column(Text, nullable=True)
    
    # 源和目标
    source_datasource_id = Column(Integer, ForeignKey("datasources.id"), nullable=False)
    target_datasource_id = Column(Integer, ForeignKey("datasources.id"), nullable=False)
    source_table = Column(String(100), nullable=False)
    target_table = Column(String(100), nullable=False)
    
    # 同步配置
    sync_type = Column(Enum(SyncType), default=SyncType.FULL)
    sync_config = Column(JSON, nullable=True)  # 同步配置 JSON
    field_mapping = Column(JSON, nullable=True)  # 字段映射 JSON
    filter_condition = Column(Text, nullable=True)  # 过滤条件
    
    # 调度配置
    is_scheduled = Column(Boolean, default=False)
    cron_expression = Column(String(100), nullable=True)
    
    # 状态信息
    status = Column(Enum(TaskStatus), default=TaskStatus.PENDING)
    is_active = Column(Boolean, default=True)
    last_run_time = Column(DateTime(timezone=True), nullable=True)
    next_run_time = Column(DateTime(timezone=True), nullable=True)
    
    # 创建信息
    creator_id = Column(Integer, ForeignKey("users.id"), nullable=False)
    created_at = Column(DateTime(timezone=True), server_default=func.now())
    updated_at = Column(DateTime(timezone=True), onupdate=func.now())
    
    # 关系
    source_datasource = relationship("DataSource", foreign_keys=[source_datasource_id])
    target_datasource = relationship("DataSource", foreign_keys=[target_datasource_id])
    creator = relationship("User")
    executions = relationship("DataSyncExecution", back_populates="task")


class DataSyncExecution(Base):
    """数据同步执行记录模型"""
    __tablename__ = "data_sync_executions"

    id = Column(Integer, primary_key=True, index=True)
    task_id = Column(Integer, ForeignKey("data_sync_tasks.id"), nullable=False)
    
    # 执行信息
    status = Column(Enum(TaskStatus), default=TaskStatus.RUNNING)
    start_time = Column(DateTime(timezone=True), server_default=func.now())
    end_time = Column(DateTime(timezone=True), nullable=True)
    duration = Column(Integer, nullable=True)  # 执行时长（秒）
    
    # 同步统计
    total_records = Column(Integer, nullable=True)
    success_records = Column(Integer, nullable=True)
    failed_records = Column(Integer, nullable=True)
    
    # 执行结果
    error_message = Column(Text, nullable=True)
    execution_log = Column(Text, nullable=True)
    
    # 关系
    task = relationship("DataSyncTask", back_populates="executions")


class DataLineage(Base):
    """数据血缘关系模型"""
    __tablename__ = "data_lineages"

    id = Column(Integer, primary_key=True, index=True)
    source_type = Column(String(50), nullable=False)  # table, column, job, script
    source_id = Column(String(100), nullable=False)   # 源对象ID
    source_name = Column(String(200), nullable=False)  # 源对象名称
    
    target_type = Column(String(50), nullable=False)
    target_id = Column(String(100), nullable=False)
    target_name = Column(String(200), nullable=False)
    
    # 关系类型
    relation_type = Column(String(50), nullable=False)  # read, write, transform
    transformation = Column(Text, nullable=True)  # 转换逻辑描述
    
    # 创建信息
    creator_id = Column(Integer, ForeignKey("users.id"), nullable=True)
    created_at = Column(DateTime(timezone=True), server_default=func.now())
    
    # 关系
    creator = relationship("User")


class DataQualityRule(Base):
    """数据质量规则模型"""
    __tablename__ = "data_quality_rules"

    id = Column(Integer, primary_key=True, index=True)
    name = Column(String(100), nullable=False)
    description = Column(Text, nullable=True)
    
    # 规则配置
    table_id = Column(Integer, ForeignKey("data_tables.id"), nullable=False)
    column_name = Column(String(100), nullable=True)  # 为空表示表级规则
    rule_type = Column(String(50), nullable=False)  # not_null, unique, range, regex, custom
    rule_config = Column(JSON, nullable=False)  # 规则配置 JSON
    
    # 检查配置
    is_active = Column(Boolean, default=True)
    severity = Column(String(20), default="warning")  # info, warning, error, critical
    
    # 创建信息
    creator_id = Column(Integer, ForeignKey("users.id"), nullable=False)
    created_at = Column(DateTime(timezone=True), server_default=func.now())
    updated_at = Column(DateTime(timezone=True), onupdate=func.now())
    
    # 关系
    table = relationship("DataTable")
    creator = relationship("User")
    results = relationship("DataQualityResult", back_populates="rule")


class DataQualityResult(Base):
    """数据质量检查结果模型"""
    __tablename__ = "data_quality_results"

    id = Column(Integer, primary_key=True, index=True)
    rule_id = Column(Integer, ForeignKey("data_quality_rules.id"), nullable=False)
    
    # 检查结果
    is_passed = Column(Boolean, nullable=False)
    error_count = Column(Integer, default=0)
    total_count = Column(Integer, default=0)
    error_rate = Column(String(10), nullable=True)  # 错误率百分比
    
    # 详细信息
    error_message = Column(Text, nullable=True)
    sample_errors = Column(JSON, nullable=True)  # 错误样例
    
    # 检查时间
    check_time = Column(DateTime(timezone=True), server_default=func.now())
    
    # 关系
    rule = relationship("DataQualityRule", back_populates="results") 